package server.database;

import java.sql.Date;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import server.database.query.IQueryResult;
import server.database.query.QueryResult;
import utils.CollectionUtils;
import utils.StringUtils;

import com.google.common.base.Joiner;
import com.google.common.collect.Lists;

public abstract class AbstractTable implements IDatabaseTable
{
	private static final Logger log = Logger.getLogger(AbstractTable.class);

	public final static String INTEGER = "INTEGER";
	public final static String STRING = "VARCHAR(255)";
	public final static String TINYINT = "TINYINT";
	public final static String FLOAT = "FLOAT";
	public final static String DATE = "DATE";

	protected final static String AUTO_INCREMENT = "AUTO_INCREMENT";
	protected final static String NOT_NULL = "NOT NULL";
	protected final static String PRIMARY_KEY = "PRIMARY KEY";
	protected final static String UNIQUE_KEY = "UNIQUE KEY";
	protected final static String KEY = "KEY";
	protected final static String UNIQUE = "UNIQUE";
	protected final static String DEFAULT_VALID = "DEFAULT '1'";

	public final static String AND = " AND ";
	public final static String OR = " OR ";
	public final static String NOT = " NOT ";
	public final static String COMMA = " , ";

	public final static String WHERE = " WHERE ";

	public final static String ENGINE_MYISAM = " ENGINE=MyISAM";

	private final static DatabaseColumn ID = new DatabaseColumn(IDatabaseTable.ID, STRING);
	private final static String EMPTY = "";

	@Override
	public void initTable() throws Exception
	{
		String sColumns = buildCreateColumns(getColumns());
		String sKeys = buildCommaSeperatedColumns(getPrimaryKey());

		String sCreate = "CREATE TABLE IF NOT EXISTS " + getTableName() + "(" + sColumns + ", " + PRIMARY_KEY + " ( " + sKeys + " )";

		sCreate += addTableKey(UNIQUE_KEY, getUniqueKey());
		sCreate += addTableKey(KEY, getKey());

		sCreate += ")";

		if (!StringUtils.isEmpty(setTableDBEngine()))
		{
			sCreate += setTableDBEngine();
		}

		DatabaseService.getInstance().execute(sCreate);

		initTableSpecific();
	}

	public String setTableDBEngine()
	{
		return null;
	}

	private String addTableKey(String sKeyType, List<DatabaseColumn> lstKey) throws Exception
	{
		if (!CollectionUtils.isEmpty(lstKey))
		{
			String sKey = buildCommaSeperatedColumns(lstKey);
			return ", " + sKeyType + "( " + sKey + " )";
		}

		return "";
	}

	public List<DatabaseColumn> getUniqueKey()
	{
		return null;
	}

	public List<DatabaseColumn> getKey()
	{
		return null;
	}

	protected void initTableSpecific() throws DatabaseException
	{
	}

	private String buildCommaSeperatedColumns(List<DatabaseColumn> lstColumnDefinitions) throws Exception
	{
		List<String> lstColumns = Lists.newArrayList();

		if (CollectionUtils.isEmpty(lstColumnDefinitions))
		{
			throw new Exception("initTable() - cant create empty table");
		}

		for (DatabaseColumn column : lstColumnDefinitions)
		{
			lstColumns.add(column.getName());
		}
		return Joiner.on(",").join(lstColumns);
	}

	private String buildCreateColumns(List<DatabaseColumn> lstColumnDefinitions) throws Exception
	{
		List<String> lstColumns = Lists.newArrayList();

		if (CollectionUtils.isEmpty(lstColumnDefinitions))
		{
			throw new Exception("initTable() - cant create empty table");
		}

		for (DatabaseColumn column : lstColumnDefinitions)
		{
			String sColumn = column.getName() + " " + column.getType();
			if (!CollectionUtils.isEmpty(column.getProperties()))
			{
				for (String sProperty : column.getProperties())
				{
					sColumn += " " + sProperty;
				}
			}
			lstColumns.add(sColumn);
		}
		return Joiner.on(",").join(lstColumns);
	}

	protected IQueryResult select(List<DatabaseColumn> lstWhereFields, List<String> lstWhereValues, List<DatabaseColumn> lstSelectFields, String sWhereDelimiter)
	{
		return select(lstWhereFields, lstWhereValues, lstSelectFields, sWhereDelimiter, null);
	}

	protected IQueryResult select(List<DatabaseColumn> lstWhereFields, List<String> lstWhereValues, List<DatabaseColumn> lstSelectFields, String sWhereDelimiter, String sWhereString)
	{
		if (lstWhereFields.size() != lstWhereValues.size())
		{
			IQueryResult queryResult = new QueryResult(IQueryResult.FAILURE, null);
			queryResult.setError("Fields and values lists does not match");
			return queryResult;
		}
		String sSelectFields = null;
		if (CollectionUtils.isEmpty(lstSelectFields))
		{
			sSelectFields = "*";
		}
		else
		{
			sSelectFields = Joiner.on(",").join(getColumnNames(lstSelectFields));
		}

		String sWhere = sWhereString;
		if (sWhere == null)
		{
			sWhere = buildWhereString(lstWhereFields, lstWhereValues, sWhereDelimiter, WHERE);
		}

		String sQuery = "SELECT " + sSelectFields + " FROM " + getTableName() + sWhere;

		IQueryResult queryResult = null;
		try
		{
			List<Map<String, String>> lstResults = DatabaseService.getInstance().query(sQuery, lstSelectFields);
			if (lstResults.size() > 0)
			{
				queryResult = new QueryResult(IQueryResult.SUCESS, lstResults);
			}
			else
			{
				queryResult = new QueryResult(IQueryResult.NO_RESULTS, null);
			}
		}
		catch (DatabaseException e)
		{
			String sError = "Query failed: " + e.getMessage();
			log.error(sError);
			queryResult = new QueryResult(IQueryResult.FAILURE, null);
			queryResult.setError(sError);
		}

		return queryResult;
	}

	@Override
	public IQueryResult insert(Map<DatabaseColumn, String> mpValues)
	{
		List<DatabaseColumn> lstFields = Lists.newArrayList();
		List<String> lstValues = Lists.newArrayList();

		for (DatabaseColumn column : mpValues.keySet())
		{
			lstFields.add(column);
			lstValues.add(mpValues.get(column));
		}

		return insert(lstFields, lstValues);
	}

	protected IQueryResult insert(List<DatabaseColumn> lstFields, List<String> lstValues)
	{
		String sFields = Joiner.on(",").join(getColumnNames(lstFields));
		List<String> wrappedValues = Lists.newArrayList();
		for (String sValue : lstValues)
		{
			wrappedValues.add(StringUtils.wrapSingleQuote(sValue));
		}
		String sValues = Joiner.on(",").join(wrappedValues);

		String sQuery = "INSERT INTO " + getTableName() + " ( " + sFields + ")" + " VALUES(" + sValues + ")";

		IQueryResult queryResult = null;
		try
		{
			int iValue = DatabaseService.getInstance().addAutoIncrement(sQuery);
			queryResult = new QueryResult(IQueryResult.SUCESS, null);
			queryResult.setAutoIncrementedValue(iValue);
		}
		catch (DatabaseException e)
		{
			String sError = "insert() - failed: " + e.getMessage();
			log.error(sError);
			queryResult = new QueryResult(IQueryResult.FAILURE, null);
			queryResult.setError(sError);
		}

		return queryResult;
	}

	private List<String> getColumnNames(List<DatabaseColumn> lstColumns)
	{
		List<String> lstNames = Lists.newArrayList();
		{
			for (DatabaseColumn column : lstColumns)
			{
				lstNames.add(column.getName());
			}
		}
		return lstNames;
	}

	private String buildWhereString(Map<DatabaseColumn, String> mpValues, String sSeperator, String sType)
	{
		List<DatabaseColumn> lstFields = Lists.newArrayList();
		List<String> lstValues = Lists.newArrayList();

		for (DatabaseColumn column : mpValues.keySet())
		{
			lstFields.add(column);
			lstValues.add(mpValues.get(column));
		}

		return buildWhereString(lstFields, lstValues, sSeperator, sType);
	}

	private String buildWhereString(List<DatabaseColumn> lstWhereFields, List<String> lstWhereValues, String sSeperator, String sType)
	{
		List<String> lstCombined = Lists.newArrayList();
		for (int i = 0; i < lstWhereFields.size(); i++)
		{
			String sField = lstWhereFields.get(i).getName();
			String sValue = StringUtils.wrapSingleQuote(lstWhereValues.get(i));
			lstCombined.add(sField + "=" + sValue);
		}

		if (lstCombined.size() > 0)
		{
			return sType + Joiner.on(sSeperator).join(lstCombined);
		}

		return "";
	}

	@Override
	public IQueryResult deleteID(String sID)
	{// TODO - should fail when no value found
		return delete(ID, sID);
	}

	public IQueryResult delete(DatabaseColumn whereField, String whereValue)
	{
		return delete(Arrays.asList(whereField), Arrays.asList(whereValue));
	}

	public IQueryResult delete(List<DatabaseColumn> lstWhereFields, List<String> lstWhereValues)
	{
		IQueryResult queryResult = null;

		String sDelete = "DELETE FROM " + getTableName() + buildWhereString(lstWhereFields, lstWhereValues, AND, WHERE);
		try
		{
			DatabaseService.getInstance().update(sDelete);
			queryResult = new QueryResult(IQueryResult.SUCESS, null);
		}
		catch (DatabaseException e)
		{
			String sError = "delete() - failed: " + e.getMessage();
			log.error(sError);
			queryResult = new QueryResult(IQueryResult.FAILURE, null);
			queryResult.setError(sError);
		}

		return queryResult;
	}

	protected IQueryResult update(List<DatabaseColumn> lstWhereFields, List<String> lstWhereValues, Map<DatabaseColumn, String> mpSetValues)
	{
		IQueryResult queryResult = null;

		String sDelete = "UPDATE " + getTableName() + " SET " + buildWhereString(mpSetValues, COMMA, EMPTY) + buildWhereString(lstWhereFields, lstWhereValues, AND, WHERE);
		try
		{
			DatabaseService.getInstance().update(sDelete);
			queryResult = new QueryResult(IQueryResult.SUCESS, null);
		}
		catch (DatabaseException e)
		{
			String sError = "update() - failed: " + e.getMessage();
			log.error(sError);
			queryResult = new QueryResult(IQueryResult.FAILURE, null);
			queryResult.setError(sError);
		}

		return queryResult;
	}

	@Override
	public IQueryResult getAllEntities(List<DatabaseColumn> lstWhereFields, List<String> lstWhereValues, String sWhereDelimiter)
	{
		return select(lstWhereFields, lstWhereValues, getColumns(), sWhereDelimiter, null);
	}

	@Override
	public IQueryResult getAllEntities(String sWhereString)
	{
		List<DatabaseColumn> lstEmptyFields = new ArrayList<DatabaseColumn>();
		List<String> lstEmptyValues = new ArrayList<String>();

		return select(lstEmptyFields, lstEmptyValues, getColumns(), null, sWhereString);
	}

	@Override
	public IQueryResult update(String sID, Map<DatabaseColumn, String> mpValues)
	{
		return update(Arrays.asList(ID), Arrays.asList(sID), mpValues);
	}

	public static String parseDateForQuery(Date date)
	{
		return "'" + String.valueOf(date) + "'";
	}
}
